﻿using Dapper;
using YinfangWeb.IDAL;
using YinfangWeb.Model;
using YinfangWeb.Common;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace YinfangWeb.DAL
{
	/// <summary>
	/// ProductData的数据操作类
	/// </summary>
	public partial class ProductDataDAO : IProductDataDAO
	{
        #region 添加单条记录
        /// <summary>
		/// 添加单条记录
		/// </summary>
		/// <param name="productData">实体</param>
        /// <param name="trans">事务</param>
		/// <returns>影响行数</returns>
        public int Insert(ProductData productData, IDbTransaction trans = null)
        {
            var sql = "INSERT INTO ProductData(ProductState, MerchantDataID, MerchantFactoryID, ProductType, ProductKindID, ProductName, ProductInfo, LinkURL, SellBase, TotalSell, TotalStock, ParamSum, IsActivity, IsRecommend, IsTop, IsMustbuy, LimitMonth, RegDateTime, RegUserID, RegFullName, EditDateTime, EditUserID, EditFullName) VALUES (@ProductState, @MerchantDataID, @MerchantFactoryID, @ProductType, @ProductKindID, @ProductName, @ProductInfo, @LinkURL, @SellBase, @TotalSell, @TotalStock, @ParamSum, @IsActivity, @IsRecommend, @IsTop, @IsMustbuy, @LimitMonth, @RegDateTime, @RegUserID, @RegFullName, @EditDateTime, @EditUserID, @EditFullName);SELECT CAST(SCOPE_IDENTITY() AS int)";

            if (trans != null)
            {
                var conn = trans.Connection;
                var productDataID = conn.Query<int>(sql, productData, trans).Single();
                productData.ProductDataID = productDataID;
                var result = 1;
                return result;
            }
            using (var conn = ConnectionFactory.CreateConnection())
            {
                conn.Open();
                var productDataID = conn.Query<int>(sql, productData).Single();
                productData.ProductDataID = productDataID;
                var result = 1;
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 添加多条记录
        /// <summary>
		/// 添加多条记录
		/// </summary>
		/// <param name="list">列表</param>
        /// <param name="trans">事务</param>
		/// <returns>影响行数</returns>
        public int Insert(List<ProductData> list, IDbTransaction trans = null)
        {
            var sql = "INSERT INTO ProductData(ProductState, MerchantDataID, MerchantFactoryID, ProductType, ProductKindID, ProductName, ProductInfo, LinkURL, SellBase, TotalSell, TotalStock, ParamSum, IsActivity, IsRecommend, IsTop, IsMustbuy, LimitMonth, RegDateTime, RegUserID, RegFullName, EditDateTime, EditUserID, EditFullName) VALUES (@ProductState, @MerchantDataID, @MerchantFactoryID, @ProductType, @ProductKindID, @ProductName, @ProductInfo, @LinkURL, @SellBase, @TotalSell, @TotalStock, @ParamSum, @IsActivity, @IsRecommend, @IsTop, @IsMustbuy, @LimitMonth, @RegDateTime, @RegUserID, @RegFullName, @EditDateTime, @EditUserID, @EditFullName)";
            var result = 0;
            if (trans != null)
            {
                var conn = trans.Connection;
                result = conn.Execute(sql, list, trans);
                return result;
            }
            using (var conn = ConnectionFactory.CreateConnection())
            {
                conn.Open();
                result = conn.Execute(sql, list);
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 编辑单条记录
        /// <summary>
		/// 编辑单条记录
		/// </summary>
		/// <param name="productData">实体</param>
        /// <param name="trans">事务</param>
		/// <returns>影响行数</returns>
        public int Update(ProductData productData, IDbTransaction trans = null)
        {
            var sql = "UPDATE ProductData SET ProductState = @ProductState, MerchantDataID = @MerchantDataID, MerchantFactoryID = @MerchantFactoryID, ProductType = @ProductType, ProductKindID = @ProductKindID, ProductName = @ProductName, ProductInfo = @ProductInfo, LinkURL = @LinkURL, SellBase = @SellBase, TotalSell = @TotalSell, TotalStock = @TotalStock, ParamSum = @ParamSum, IsActivity = @IsActivity, IsRecommend = @IsRecommend, IsTop = @IsTop, IsMustbuy = @IsMustbuy, LimitMonth = @LimitMonth, RegDateTime = @RegDateTime, RegUserID = @RegUserID, RegFullName = @RegFullName, EditDateTime = @EditDateTime, EditUserID = @EditUserID, EditFullName = @EditFullName WHERE ProductDataID = @ProductDataID";
            var result = 0;
            if (trans != null)
            {
                var conn = trans.Connection;
                result = conn.Execute(sql, productData, trans);
                return result;
            }
            using (var conn = ConnectionFactory.CreateConnection())
            {
                conn.Open();
                result = conn.Execute(sql, productData);
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 编辑多条记录
        /// <summary>
		/// 编辑多条记录
		/// </summary>
		/// <param name="list">实体列表</param>
        /// <param name="trans">事务</param>
		/// <returns>影响行数</returns>
        public int Update(List<ProductData> list, IDbTransaction trans = null)
        {
            var sql = "UPDATE ProductData SET ProductState = @ProductState, MerchantDataID = @MerchantDataID, MerchantFactoryID = @MerchantFactoryID, ProductType = @ProductType, ProductKindID = @ProductKindID, ProductName = @ProductName, ProductInfo = @ProductInfo, LinkURL = @LinkURL, SellBase = @SellBase, TotalSell = @TotalSell, TotalStock = @TotalStock, ParamSum = @ParamSum, IsActivity = @IsActivity, IsRecommend = @IsRecommend, IsTop = @IsTop, IsMustbuy = @IsMustbuy, LimitMonth = @LimitMonth, RegDateTime = @RegDateTime, RegUserID = @RegUserID, RegFullName = @RegFullName, EditDateTime = @EditDateTime, EditUserID = @EditUserID, EditFullName = @EditFullName WHERE ProductDataID = @ProductDataID";
            var result = 0;
            if (trans != null)
            {
                var conn = trans.Connection;
                result = conn.Execute(sql, list, trans);
                return result;
            }
            using (var conn = ConnectionFactory.CreateConnection())
            {
                conn.Open();
                result = conn.Execute(sql, list);
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 删除单条记录
        /// <summary>
		/// 删除单条记录
		/// </summary>
		/// <param name="productDataID">主键</param>
        /// <param name="trans">事务</param>
		/// <returns>影响行数</returns>
        public int Delete(int productDataID, IDbTransaction trans = null)
        {
            var sql = "DELETE ProductData WHERE ProductDataID = @ProductDataID";
            var result = 0;
            if (trans != null)
            {
                var conn = trans.Connection;
                result = conn.Execute(sql, new { ProductDataID = productDataID }, trans);
                return result;
            }
            using (var conn = ConnectionFactory.CreateConnection())
            {
                conn.Open();
                result = conn.Execute(sql, new { ProductDataID = productDataID });
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 删除单条记录
        /// <summary>
		/// 删除单条记录
		/// </summary>
		/// <param name="productData">实体</param>
        /// <param name="trans">事务</param>
		/// <returns>影响行数</returns>
        public int Delete(ProductData productData, IDbTransaction trans = null)
        {
            var sql = "DELETE ProductData WHERE ProductDataID = @ProductDataID";
            var result = 0;
            if (trans != null)
            {
                var conn = trans.Connection;
                result = conn.Execute(sql, productData, trans);
                return result;
            }
            using (var conn = ConnectionFactory.CreateConnection())
            {
                conn.Open();
                result = conn.Execute(sql, productData);
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 获取单条记录
        /// <summary>
		/// 获取单条记录
		/// </summary>
		/// <param name="productDataID">主键</param>
		/// <returns>实体</returns>
        public ProductData GetInfo(int productDataID)
        {
            var sql = "SELECT * FROM ProductData WHERE ProductDataID = @ProductDataID";
            using (var conn = ConnectionFactory.CreateConnection())
            {
                var model = conn.QueryFirstOrDefault<ProductData>(sql, new { ProductDataID = productDataID });
                return model;
            }
        }
        #endregion
        #region 获取全部记录列表
        /// <summary>
		/// 获取全部记录列表
		/// </summary>
		/// <returns>实体列表</returns>
        public List<ProductData> GetList()
        {
            var sql = "SELECT * FROM ProductData";
            using (var conn = ConnectionFactory.CreateConnection())
            {
                var q = conn.Query<ProductData>(sql);
                return q.ToList();
            }
        }
        #endregion
        #region 分页列表
        /// <summary>
        /// 分页列表
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">页面大小</param>
        /// <param name="orderBy">排序规则</param>
        /// <param name="whereClause">条件语句</param>
        /// <returns>分页列表</returns>
        public PagedList<ProductData> GetList(int pageIndex, int pageSize, string whereClause = null, string orderBy = null)
        {
            pageIndex--;
            if (!string.IsNullOrEmpty(whereClause))
            {
                whereClause = " WHERE " + whereClause;
            }
            if (string.IsNullOrEmpty(orderBy))
            {
                orderBy = "ProductDataID DESC";
            }
            var startItem = pageIndex * pageSize + 1;
            var endItem = (pageIndex + 1) * pageSize;

            var sql = $@"SELECT COUNT(ProductDataID) FROM ProductData {whereClause} 
SELECT *
FROM(SELECT ROW_NUMBER() OVER(ORDER BY {orderBy}) AS RowNum, *
          FROM ProductData
          {whereClause}
        ) AS result
WHERE RowNum >= {startItem}
    AND RowNum <= {endItem}
ORDER BY RowNum";
            using (var conn = ConnectionFactory.CreateConnection())
            {
                conn.Open();
                int count = 0;
                var list = new List<ProductData>();
                using (var q = conn.QueryMultiple(sql))
                {
                    count = q.Read<int>().Single();
                    list = q.Read<ProductData>().ToList();
                }
                conn.Close();

                var pagedList = new PagedList<ProductData>(list, pageIndex, pageSize, count);
                return pagedList;
            }
        }
        #endregion
        #region 数据操作类
		/// <summary>
		/// ProductData的数据操作类
		/// </summary>
		public ProductDataDAO()
		{
		}
        #endregion
	}
}

